package preprocess;


import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.List;

import au.com.bytecode.opencsv.CSVReader;


public class CityCountryFiller {
	
	
	public static void main(String [] args) throws Exception{
		CityCountryFiller fcs = new CityCountryFiller();
		fcs.fillCountries("countries.csv");
	}
	
	
	public void fillCountries(String in) {
		try{
			
			CSVReader reader = new CSVReader(new FileReader(in),';');
			
			List<String[]> list = null;
			list = reader.readAll();
			
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/nstweb?user=root&password=root");
		    Statement s = c.createStatement();
		    
		    System.out.println(list.size());
		    
		    for(int i=0;i<list.size();i++){
		    	String name = list.get(i)[1];
		    	String country = list.get(i)[0];
		    	String query = "INSERT INTO countries VALUES ('"+country+"','"+name+"');";
		    	s.executeUpdate(query);
		    }
		    
		    
		    s.close();
		    c.close();
	    
		}catch(Exception e){
			e.printStackTrace();
		
		}
		
	}
}
